﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using Portal.Utility;
using Portal.Exam.Model;
using Portal.Core;
using Portal.Web.Utility;


namespace Portal.Exam.Bussiness
{
    public class ExamSegmentBll
    {
        string GetExamConnectionString(string SiteId)
        {
            SiteParam OSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string ExamConnectionString = WebConfig.GetConfigFromXml(OSiteParam, "ExamConnectionString");
            return ExamConnectionString;
        }

        public ExamSegmentCls CreateModel(string SiteId, string ExamSegmentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            string Query = " select TableExamSegment.* from TableExamSegment where ExamSegmentId = '" + ExamSegmentId + "'";
            SqlCommand SqlCommand = new SqlCommand(Query, new SqlConnection(GetExamConnectionString(SiteId)));
            SqlCommand.Connection.Open();
            SqlCommand.CommandText = Query;
            DataTable dtResult = new DataTable();
            SqlDataAdapter sqlda = new SqlDataAdapter(SqlCommand);
            sqlda.Fill(dtResult);

            if (dtResult.Rows.Count == 0)
            {
                return null;
            }

            ExamSegmentCls tExamSegmentCls = new ExamSegmentCls();
            tExamSegmentCls.ExamSegmentId = (string)dtResult.Rows[0]["ExamSegmentId"];
            tExamSegmentCls.ExamSegmentName = (string)dtResult.Rows[0]["ExamSegmentName"];
            tExamSegmentCls.ExamType = (string)dtResult.Rows[0]["ExamType"];
            tExamSegmentCls.Active = (int)dtResult.Rows[0]["Active"];

            try
            {
                tExamSegmentCls.Year = (int)dtResult.Rows[0]["Year"];
            }
            catch { }

            try
            {
                tExamSegmentCls.FromDate = (DateTime)dtResult.Rows[0]["FromDate"];
            }
            catch { }
            try
            {
                tExamSegmentCls.ToDate = (DateTime)dtResult.Rows[0]["ToDate"];
            }
            catch { }

            return tExamSegmentCls;
        }


        public DataTable ReadingExamSegmentForAccountType(
           string SiteId,
            string AccountType,
           string ExamType,
           int Active,
           string Keyword)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select * from TableExamSegment where ExamType='" + ExamType + "' and AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and Active=" + Active + " and AccountType='" + AccountType + "'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamSegmentName like N'%" + Keyword + "%'";
            }
            Query += " order by [Year] DESC";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public DataTable ReadingExamSegment(
            string SiteId,
            string ExamType,
            int Active,
            string Keyword)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select * from TableExamSegment where ExamType='" + ExamType + "' and AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and Active=" + Active;
            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamSegmentName like N'%" + Keyword + "%'";
            }
            Query += " order by [Year] DESC";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        

        public DataTable ReadingExamSegmentForAccountType(
            string SiteId,
            string ExamType,
            int Active,
            string Keyword,
            string AccountType)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            if (Keyword == null) Keyword = "";
            DataTable dataTable = new DataTable();
            Keyword = FunctionUtilities.CorrectForSearch(Keyword);
            string Query = "select * from TableExamSegment where ExamType='" + ExamType + "' and AssetCode='" + WebSessionUtility.GetCurrentLoginUser().AssetCode + "' and Active=" + Active + " and AccountType='" + AccountType + "'";
            if (Keyword.Trim().Length > 0)
            {
                Query += " and ExamSegmentName like N'%" + Keyword + "%'";
            }
            Query += " order by [Year] DESC";

            SqlDataAdapter da = new SqlDataAdapter(Query, new SqlConnection(GetExamConnectionString(SiteId)));

            da.Fill(dataTable);
            return dataTable;
        }

        public string CreateExamSegment(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string AccountType,
            string ExamSegmentName,
            int Year,
            string ExamType,
            DateTime FromDate,
            DateTime ToDate)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;
            string NewExamSegmentId = System.Guid.NewGuid().ToString();

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Insert into TableExamSegment(ExamSegmentId,ExamSegmentName,Year,ExamType,FromDate,ToDate,AssetCode,[Active],AccountType) values (@ExamSegmentId,@ExamSegmentName,@Year,@ExamType,@FromDate,@ToDate,@AssetCode,1,@AccountType)";

            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamSegmentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSegmentId"].Value = NewExamSegmentId;

            TempSqlCommand.Parameters.Add("@ExamSegmentName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSegmentName"].Value = ExamSegmentName;

            TempSqlCommand.Parameters.Add("@Year", SqlDbType.Int);
            TempSqlCommand.Parameters["@Year"].Value = Year;

            TempSqlCommand.Parameters.Add("@FromDate", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@FromDate"].Value = FromDate;

            TempSqlCommand.Parameters.Add("@ToDate", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@ToDate"].Value = ToDate;

            TempSqlCommand.Parameters.Add("@ExamType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamType"].Value = ExamType;

            TempSqlCommand.Parameters.Add("@AssetCode", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AssetCode"].Value = WebSessionUtility.GetCurrentLoginUser().AssetCode;

            TempSqlCommand.Parameters.Add("@AccountType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@AccountType"].Value = AccountType;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
            return NewExamSegmentId;
        }

        public void SetStatus(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamSegmentId,
            int Active)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Update TableExamSegment set [Active]=@Active where ExamSegmentId=@ExamSegmentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamSegmentId", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamSegmentId"].Value = ExamSegmentId;

            TempSqlCommand.Parameters.Add("@Active", SqlDbType.Int);
            TempSqlCommand.Parameters["@Active"].Value = Active;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }

        public void UpdateExamSegment(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamSegmentId,
            string ExamSegmentName,
            int Year,
            string ExamType,
            DateTime FromDate,
            DateTime ToDate)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);
            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = "Update TableExamSegment set ExamSegmentName=@ExamSegmentName, FromDate=@FromDate,ToDate=@ToDate,Year=@Year,ExamType=@ExamType where ExamSegmentId=@ExamSegmentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamSegmentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSegmentId"].Value = ExamSegmentId;

            TempSqlCommand.Parameters.Add("@ExamSegmentName", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSegmentName"].Value = ExamSegmentName;

            TempSqlCommand.Parameters.Add("@FromDate", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@FromDate"].Value = FromDate;

            TempSqlCommand.Parameters.Add("@ToDate", SqlDbType.DateTime);
            TempSqlCommand.Parameters["@ToDate"].Value = ToDate;


            TempSqlCommand.Parameters.Add("@Year", SqlDbType.Int);
            TempSqlCommand.Parameters["@Year"].Value = Year;

            TempSqlCommand.Parameters.Add("@ExamType", SqlDbType.VarChar);
            TempSqlCommand.Parameters["@ExamType"].Value = ExamType;


            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }


        public void DeleteExamSegment(
            SqlTransaction _SqlTransaction,
            string SiteId,
            string ExamSegmentId)
        {
            SiteParam oSiteParam = WebEnvironments.CreateSiteParam(SiteId);

            SqlConnection SqlConnection;
            bool HasParentTransaction = false;

            if (_SqlTransaction == null)
            {
                SqlConnection = new SqlConnection(GetExamConnectionString(SiteId));
                SqlConnection.Open();
                _SqlTransaction = SqlConnection.BeginTransaction();
            }
            else
            {
                HasParentTransaction = true;
            }

            string Query = " Delete TableExamSegment where ExamSegmentId=@ExamSegmentId";
            SqlCommand TempSqlCommand = _SqlTransaction.Connection.CreateCommand();
            TempSqlCommand.Transaction = _SqlTransaction;
            TempSqlCommand.CommandText = Query;
            TempSqlCommand.Parameters.Add("@ExamSegmentId", SqlDbType.NVarChar);
            TempSqlCommand.Parameters["@ExamSegmentId"].Value = ExamSegmentId;

            try
            {
                TempSqlCommand.ExecuteNonQuery();
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Commit();
                }
            }
            catch (Exception ex)
            {
                if (HasParentTransaction == false)
                {
                    _SqlTransaction.Rollback();
                }
                throw (ex);
            }

            if (HasParentTransaction == false)
            {
                TempSqlCommand.Connection.Close();
            }
        }
    }
}
